package CustomClassFile.yueli;

import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.SplitPageParaBean;
import weaver.general.SplitPageUtil;
import weaver.general.Util;

public class Jiaban {
	public static final Log logger = LogFactory.getLog(Jiaban.class);
	public static Map<String,Object> jiabanTotal(int userid,String year) {
		if (StringUtils.isEmpty(year)) {
			year="";
		}
		Map<String,Object> map=new HashMap<String,Object>();
		String sql="select jblx,isnull(sum(hjsqxss),0)as hjsqxsstotal,isnull(sum(hjsqts),0)as hjsqtstotal,isnull(sum(hjsjxss),0) as hjsjxsstotal,isnull(sum(hjsjts),0)as hjsjtstotal from uf_zsws_jbdjb  "
				+ "where sqr=? and jbrq like '"+year+"%' group by jblx";
		RecordSet rs=new RecordSet();
		rs.executeQuery(sql, userid);
		//初始化3种类型为0
		Map<String,Object> mapzero=new HashMap<String,Object>();
		mapzero.put("hjsqxsstotal", "0");
		mapzero.put("hjsqtstotal", "0");
		mapzero.put("hjsjxsstotal", "0");
		mapzero.put("hjsjtstotal", "0");
		Map<String,Object> mapzero1=new HashMap<String,Object>();
		mapzero1.put("hjsqxsstotal", "0");
		mapzero1.put("hjsqtstotal", "0");
		mapzero1.put("hjsjxsstotal", "0");
		mapzero1.put("hjsjtstotal", "0");
		Map<String,Object> mapzero2=new HashMap<String,Object>();
		mapzero2.put("hjsqxsstotal", "0");
		mapzero2.put("hjsqtstotal", "0");
		mapzero2.put("hjsjxsstotal", "0");
		mapzero2.put("hjsjtstotal", "0");
		map.put("0", mapzero);
		map.put("1", mapzero1);
		map.put("2", mapzero2);
		
		while(rs.next()) {
			Map<String,Object> map2=new HashMap<String,Object>();
			map2.put("hjsqxsstotal", rs.getString("hjsqxsstotal"));
			map2.put("hjsqtstotal", rs.getString("hjsqtstotal"));
			map2.put("hjsjxsstotal", rs.getString("hjsjxsstotal"));
			map2.put("hjsjtstotal", rs.getString("hjsjtstotal"));
			map.put(rs.getString("jblx"),map2);
		}
		
		return map;
	}
	
	public static JSONObject selectRecord(int userid,String year,int pageNo,int pageSize) {
		
		String sql="select isnull(card1,'缺卡') as card1,isnull(card2,'缺卡')as card2,isnull(card3,'缺卡')as card3,isnull(card4,'缺卡')as card4,"
				+ "isnull(hjsjts,0) as hjsjts,isnull(hjsjxss,0)as hjsjxss,isnull(hjsqts,0)as hjsqts,isnull(hjsqxss,0)as hjsqxss,id,"
				+ "(case jbkz when 0 then '转补休' when 1 then '转加班费' end) as jbkz ,jblx,jbrq,"
				+ "jbsy,jssj,kssj,requestId,sqbm,sqr,sqrq from uf_zsws_jbdjb  where sqr="+userid;
		if (!StringUtils.isEmpty(year)) {
			sql+=" and CONVERT(varchar(4),jbrq,20)='"+year+"'";
		}
		logger.info(sql);
		/*分页查询代码，标准代码*/
		SplitPageParaBean spp = new SplitPageParaBean();
		spp.setSqlFrom("("+sql+") t1");
		spp.setPrimaryKey("id");
		spp.setBackFields("*");
		spp.setSqlOrderBy("t1.jbrq");	//排序字段
		spp.setSortWay(1);	//0 升序   1 降序
		SplitPageUtil spu = new SplitPageUtil();
		spu.setSpp(spp);
		RecordSet rs = spu.getCurrentPageRs(pageNo,pageSize);
		/*将查询结果按照 字段名作为key，字段值作为value 动态封装成json，标准代码*/
		int totalSize = spu.getRecordCount();
		JSONArray datas = new JSONArray();
		String[] columnNames = rs.getColumnName();
		while(rs.next()){
			JSONObject jsonObj = new JSONObject();
			for(String columnName : columnNames){
				jsonObj.put(columnName, Util.formatMultiLang(rs.getString(columnName)));
			}
			datas.add(jsonObj);
		}
		JSONObject resultObj = new JSONObject();
		resultObj.put("totalSize", totalSize);
		resultObj.put("datas", JSONArray.fromObject(datas));
		return resultObj;
	}
	
}
